from Tools import *
from Types import *
import sqlite3
import copy
import os.path

class DB:
    def __init__(self, dbPath):
        self.db = None
        self.curs = None
        self.dbPath = dbPath
        
    def init(self):
        ''' 成功返回0，否則返回-1'''
        createDBFlag = False
        if os.path.exists(self.dbPath) == False:
            createDBFlag = True
        self.db = sqlite3.connect(self.dbPath)
        self.curs = self.db.cursor()
        if createDBFlag == True:
            ret = self.createDatabase()
            if ret == -1:
                return -1
        return 0
    
    def destroy(self):
        self.db.close()
        
    def createDatabase(self):
        '''創建數據庫并寫入基本數據
        如果失敗則刪除現有數據庫文件
        成功返回0，否則返回-1'''
        try:
            dbFile = open(Global.rootDir + Global.CREATE_DB_SCRIPT_PATH, mode='r', encoding='UTF-8')
            dbFileStr = dbFile.read()
            dbFile.close()
            self.curs.executescript(dbFileStr)
            self.db.commit()
        except Exception as e:
            print(e)
            print('ERROR: CREATE DATABASE FAILED!')
            self.db.close()
            os.remove(self.dbPath)
            return -1
        return 0

    def getCategorys(self):
        # 失敗返回None
        cat = Category()
        cat.incomeCat1 = self.__getIncomeCat1()
        cat.paymentCat1 = self.__getPaymentCat1()
        cat.paymentCat2 = self.__getPaymentCat2()
        cat.transferCat1 = self.__getTransferCat1()
        if len(cat.incomeCat1) <= 1 \
        or len(cat.paymentCat1) <= 1 \
        or len(cat.paymentCat2) <= 1 \
        or len(cat.transferCat1) <= 1:
            raise ValueError('One or more Category is empty!')
        return cat

    def getAccounts(self):
        '''返回列表，每個元素Account
        按照id排序，第0個不用'''
        accountList = [Account()]
        dbStr = 'SELECT ID, NAME, SHOWABLE FROM ACCOUNT ORDER BY ID ASC;'
        ret = self.curs.execute(dbStr)
        for i in ret:
            account = Account()
            account.id = i[0]
            account.name = i[1]
            account.showable = i[2]
            accountList.append(account)
        return accountList

    # 執行一條SQL語句
    # 返回sqlite3.cursor.execute()的返回結果
    def executeSQL(self, sqlStr):
        ret = self.curs.execute(sqlStr)
        self.db.commit()
        return ret
    
    def addIncomeRecord(self, incomeRecord):
        dbStrs = []
        dbStrs.append('BEGIN TRANSACTION')
        dbStrs.append('INSERT INTO INCOME_RECORD VALUES(NULL,%d,%d,%d,%d,"%s");'%\
            (incomeRecord.accountId, incomeRecord.time, \
            incomeRecord.amount, incomeRecord.cat1Id, incomeRecord.description))
        dbStr = 'UPDATE ACCOUNT_BALANCE SET BALANCE = BALANCE + %d WHERE ID = %d'%(incomeRecord.amount, incomeRecord.accountId)
        dbStrs.append(dbStr)
        dbStrs.extend(self.genStatisticRecordSQLStringForAddRecord(True, incomeRecord))
        dbStrs.append('COMMIT TRANSACTION')
        for dbStr in dbStrs:
            self.curs.execute(dbStr)
        self.db.commit()
        
    def addPaymentRecord(self, paymentRecord):
        dbStrs = []
        dbStrs.append('BEGIN TRANSACTION')
        dbStrs.append('INSERT INTO PAYMENT_RECORD VALUES(NULL,%d,%d,%d,%d,"%s");'%\
            (paymentRecord.accountId, paymentRecord.time, \
            paymentRecord.amount, paymentRecord.cat2Id, paymentRecord.description))
        dbStr = 'UPDATE ACCOUNT_BALANCE SET BALANCE = BALANCE - %s WHERE ID = %d'%(paymentRecord.amount, paymentRecord.accountId)
        dbStrs.append(dbStr)
        dbStrs.extend(self.genStatisticRecordSQLStringForAddRecord(False, paymentRecord))
        dbStrs.append('COMMIT TRANSACTION')
        for dbStr in dbStrs:
            self.curs.execute(dbStr)
        self.db.commit()

    def addTransferRecord(self, aofRecordtransferRecord):
        dbStrs = []
        dbStrs.append('BEGIN TRANSACTION')
        dbStr = 'INSERT INTO TRANSFER_RECORD VALUES(NULL,%d,%d,%d,%d,%d,"%s");'%\
            (aofRecordtransferRecord.time, aofRecordtransferRecord.amount, aofRecordtransferRecord.cat1Id,\
            aofRecordtransferRecord.srcAccountId, aofRecordtransferRecord.dstAccountId, aofRecordtransferRecord.description)
        dbStrs.append(dbStr)
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = BALANCE - %d WHERE ID = %d'%(aofRecordtransferRecord.amount, aofRecordtransferRecord.srcAccountId))
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = BALANCE + %d WHERE ID = %d'%(aofRecordtransferRecord.amount, aofRecordtransferRecord.dstAccountId))
        dbStrs.append('COMMIT TRANSACTION')
        for dbStr in dbStrs:
            self.curs.execute(dbStr)
        self.db.commit()

    def deleteIncomeRecord(self, recordId):
        #沒有考慮更新ACCOUNT_BALANCE和DAILY/MONTHLY/ANAUALLY_STATIC_RECORD
        incomeRecord = self.getIncomeRecord(recordId)
        dbStrs = []
        dbStrs.append('DELETE FROM INCOME_RECORD WHERE RCD_ID = %d'%(recordId))
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = BALANCE - %d WHERE ID = %d'%(incomeRecord.amount, incomeRecord.accountId))
        dbStrs.extend(self.genStatisticRecordSQLStringForDeleteRecord(True, incomeRecord))
        for dbStr in dbStrs:
            self.curs.execute(dbStr)
        self.db.commit()

    def deletePaymentRecord(self, recordId):
        paymentRecord = self.getPaymentRecord(recordId)
        dbStrs = []
        dbStrs.append('DELETE FROM PAYMENT_RECORD WHERE RCD_ID = %d'%(recordId))
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = BALANCE + %d WHERE ID = %d'%(paymentRecord.amount, paymentRecord.accountId))
        dbStrs.extend(self.genStatisticRecordSQLStringForDeleteRecord(False, paymentRecord))
        for dbStr in dbStrs:
            self.curs.execute(dbStr)
        self.db.commit()

    def deleteTransferRecord(self, recordId):
        transferRecord = self.getTransferRecord(recordId)
        dbStrs = []
        dbStrs.append('DELETE FROM TRANSFER_RECORD WHERE RCD_ID = %d'%(recordId))
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = BALANCE + %d WHERE ID = %d'%(transferRecord.amount, aofRecordtransferRecord.srcAccountId))
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = BALANCE - %d WHERE ID = %d'%(transferRecord.amount, aofRecordtransferRecord.dstAccountId))
        for dbStr in dbStrs:
            self.curs.execute(dbStr)
        self.db.commit()

    def getAccountBalanceData(self):
        '''獲取各个账户的余额
        返回列表，內容為：[AccountName,Balance]'''
        returnList = []
        dbStr = 'SELECT ACCOUNT.NAME,BALANCE FROM ACCOUNT,ACCOUNT_BALANCE WHERE ACCOUNT.ID = ACCOUNT_BALANCE.ID AND ACCOUNT.SHOWABLE = 1;'
        ret = self.curs.execute(dbStr)
        for i in ret :            
            returnList.append([i[0], i[1]])
        return returnList

    def genStatisticRecordSQLStringForAddRecord(self, isIncome, record):
        '''添加一條記錄時，根據記錄生成操作STAT_RECORD的SQL語句
        isIncome: True代表收入，False代表支出
        record: IncomeRecord或者PaymentRecord之一
        返回SQL字符串列表'''
        ret = timestampToDailyMonthlyAnnuallyTimestamp(record.time)
        dailyTimestamp = ret['daily']
        monthTimestamp = ret['monthly']
        annuallyTimestamp = ret['annually']
        # 生成每日記錄的SQL
        dbStr = 'SELECT COUNT(*) FROM DAILY_STAT_RECORD WHERE TIMEME = %d'%(dailyTimestamp)
        ret = self.curs.execute(dbStr)
        if ret.fetchone()[0] == 0:
            if isIncome == True:
                dailyDBStr = 'INSERT INTO DAILY_STAT_RECORD VALUES(%d,%d,0)'%(dailyTimestamp, record.amount)
            else:
                dailyDBStr = 'INSERT INTO DAILY_STAT_RECORD VALUES(%d,0,%d)'%(dailyTimestamp, record.amount)
        else:
            if isIncome == True:
                dailyDBStr = 'UPDATE DAILY_STAT_RECORD SET INCOME = INCOME + %d WHERE TIMEME = %d'%(record.amount, dailyTimestamp)
            else:
                dailyDBStr = 'UPDATE DAILY_STAT_RECORD SET PAYMENT = PAYMENT + %d WHERE TIMEME = %d'%(record.amount, dailyTimestamp)
        # 生成每月記錄的SQL
        dbStr = 'SELECT COUNT(*) FROM MONTHLY_STAT_RECORD WHERE TIMEME = %d'%(monthTimestamp)
        ret = self.curs.execute(dbStr)
        if ret.fetchone()[0] == 0:
            if isIncome == True:
                monthlyDBStr = 'INSERT INTO MONTHLY_STAT_RECORD VALUES(%d,%d,0)'%(monthTimestamp, record.amount)
            else:
                monthlyDBStr = 'INSERT INTO MONTHLY_STAT_RECORD VALUES(%d,0,%d)'%(monthTimestamp, record.amount)
        else:
            if isIncome == True:
                monthlyDBStr = 'UPDATE MONTHLY_STAT_RECORD SET INCOME = INCOME + %d WHERE TIMEME = %d'%(record.amount, monthTimestamp)
            else:
                monthlyDBStr = 'UPDATE MONTHLY_STAT_RECORD SET PAYMENT = PAYMENT + %d WHERE TIMEME = %d'%(record.amount, monthTimestamp)
        # 生成每年記錄的SQL
        dbStr = 'SELECT COUNT(*) FROM ANNUALLY_STAT_RECORD WHERE TIMEME = %d'%(annuallyTimestamp)
        ret = self.curs.execute(dbStr)
        if ret.fetchone()[0] == 0:
            if isIncome == True:
                annuallyDBStr = 'INSERT INTO ANNUALLY_STAT_RECORD VALUES(%d,%d,0)'%(annuallyTimestamp, record.amount)
            else:
                annuallyDBStr = 'INSERT INTO ANNUALLY_STAT_RECORD VALUES(%d,0,%d)'%(annuallyTimestamp, record.amount)
        else:
            if isIncome == True:
                annuallyDBStr = 'UPDATE ANNUALLY_STAT_RECORD SET INCOME = INCOME + %d WHERE TIMEME = %d'%(record.amount, annuallyTimestamp)
            else:
                annuallyDBStr = 'UPDATE ANNUALLY_STAT_RECORD SET PAYMENT = PAYMENT + %d WHERE TIMEME = %d'%(record.amount, annuallyTimestamp)
        return [dailyDBStr, monthlyDBStr, annuallyDBStr]

    def genStatisticRecordSQLStringForDeleteRecord(self, isIncome, record):
        '''刪除一條記錄時，根據記錄生成操作STAT_RECORD的SQL語句
        isIncome: True代表收入，False代表支出
        record: IncomeRecord或者PaymentRecord之一
        返回SQL字符串列表'''
        ret = timestampToDailyMonthlyAnnuallyTimestamp(record.time)
        dailyTimestamp = ret['daily']
        monthTimestamp = ret['monthly']
        annuallyTimestamp = ret['annually']
        # 生成每日記錄的SQL
        dbStr = 'SELECT COUNT(*) FROM DAILY_STAT_RECORD WHERE TIMEME = %d'%(dailyTimestamp)
        ret = self.curs.execute(dbStr)
        if ret.fetchone()[0] == 0:
            raise ValueError('RECORD IN DAILY_STAT_RECORD NOT EXIST!')
        else:
            if isIncome == True:
                dailyDBStr = 'UPDATE DAILY_STAT_RECORD SET INCOME = INCOME - %d WHERE TIMEME = %d'%(record.amount, dailyTimestamp)
            else:
                dailyDBStr = 'UPDATE DAILY_STAT_RECORD SET PAYMENT = PAYMENT - %d WHERE TIMEME = %d'%(record.amount, dailyTimestamp)
        # 生成每月記錄的SQL
        dbStr = 'SELECT COUNT(*) FROM MONTHLY_STAT_RECORD WHERE TIMEME = %d'%(monthTimestamp)
        ret = self.curs.execute(dbStr)
        if ret.fetchone()[0] == 0:
            raise ValueError('RECORD IN MONTHLY_STAT_RECORD NOT EXIST!')
        else:
            if isIncome == True:
                monthlyDBStr = 'UPDATE MONTHLY_STAT_RECORD SET INCOME = INCOME - %d WHERE TIMEME = %d'%(record.amount, monthTimestamp)
            else:
                monthlyDBStr = 'UPDATE MONTHLY_STAT_RECORD SET PAYMENT = PAYMENT - %d WHERE TIMEME = %d'%(record.amount, monthTimestamp)
        # 生成每年記錄的SQL
        dbStr = 'SELECT COUNT(*) FROM ANNUALLY_STAT_RECORD WHERE TIMEME = %d'%(annuallyTimestamp)
        ret = self.curs.execute(dbStr)
        if ret.fetchone()[0] == 0:
            raise ValueError('RECORD IN ANNUALLY_STAT_RECORD NOT EXIST!')
        else:
            if isIncome == True:
                annuallyDBStr = 'UPDATE ANNUALLY_STAT_RECORD SET INCOME = INCOME - %d WHERE TIMEME = %d'%(record.amount, annuallyTimestamp)
            else:
                annuallyDBStr = 'UPDATE ANNUALLY_STAT_RECORD SET PAYMENT = PAYMENT - %d WHERE TIMEME = %d'%(record.amount, annuallyTimestamp)
        return [dailyDBStr, monthlyDBStr, annuallyDBStr]

    def getDailyStatisticRecordInRange(self, beginTime, endTime):
        '''獲取指定時間範圍內的天級別統計記錄
        起止時間範圍內沒有數據時間段的記錄記為零
        返回列表，列表的每一項為：[時間戳, 收入, 支出]'''
        dbStr = 'SELECT TIMEME, INCOME, PAYMENT FROM DAILY_STAT_RECORD WHERE TIMEME >= %d AND TIMEME <= %d ORDER BY TIMEME ASC'%(beginTime, endTime)
        ret = self.curs.execute(dbStr)
        records = []
        for i in ret:
            records.append([i[0], i[1], i[2]])
        records = supplyStatisticRecord(records, beginTime, endTime, TimeLength.Day)
        return records
        
    def getMonthlyStatisticRecordInRange(self, beginTime, endTime):
        '''獲取指定時間範圍內的月級別統計記錄'''
        dbStr = 'SELECT TIMEME, INCOME, PAYMENT FROM MONTHLY_STAT_RECORD WHERE TIMEME >= %d AND TIMEME <= %d ORDER BY TIMEME ASC'%(beginTime, endTime)
        ret = self.curs.execute(dbStr)
        records = []
        for i in ret:
            records.append([i[0], i[1], i[2]])
        records = supplyStatisticRecord(records, beginTime, endTime, TimeLength.Month)
        return records
    
    def getAnnuallyStatisticRecordInRange(self, beginTime, endTime):
        '''獲取指定時間範圍內的年級別統計記錄'''
        dbStr = 'SELECT TIMEME, INCOME, PAYMENT FROM ANNUALLY_STAT_RECORD WHERE TIMEME >= %d AND TIMEME <= %d ORDER BY TIMEME ASC'%(beginTime, endTime)
        ret = self.curs.execute(dbStr)
        records = []
        for i in ret:
            records.append([i[0], i[1], i[2]])
        records = supplyStatisticRecord(records, beginTime, endTime, TimeLength.Year)
        return records

    def setBudget(self, budgetList):
        '''设置一级支出分类的预算
        budgetList: 各个分类的个月预算和年度预算，结构为：[[cat1Id, [m1, m2, ... , m12, year]], ...]
        cat1Id為0表示TATAL
        '''
        dbStrs = []
        for item in budgetList:
            cat1Id = item[0]
            item = item[1]
            dbStr = 'UPDATE BUDGET_PLAN SET MON1=%d,MON2=%d,MON3=%d,MON4=%d,MON5=%d,MON6=%d,MON7=%d,MON8=%d,MON9=%d,MON10=%d,MON11=%d,MON12=%d,ALL_YEAR=%d WHERE PAYMENT_CAT1_ID=%d'%\
                (item[0],item[1],item[2],item[3],item[4],item[5],item[6],item[7],item[8],item[9],item[10],item[11],item[12],cat1Id)
            dbStrs.append(dbStr)
        for dbStr in dbStrs:
            self.curs.execute(dbStr)
        self.db.commit()

    def getBudgetPlanWithoutDeleteCat1(self):
        '''獲取當前設定的各個一級支出的預算，不包含已刪除的一級支出分類
        格式為：[[NAME, CAT1_ID, MON1 ~ MON12, ALL_YEAR] ...]
        第0個元素為總預算
        返回的列表的下標與CAT1_ID相同'''
        sqlStr = 'SELECT PAYMENT_CAT1.NAME, BUDGET_PLAN.* FROM PAYMENT_CAT1, BUDGET_PLAN \
            WHERE PAYMENT_CAT1.CAT1_ID = BUDGET_PLAN.PAYMENT_CAT1_ID AND PAYMENT_CAT1.SHOWABLE = 1 \
            UNION SELECT "TOTAL", * FROM BUDGET_PLAN WHERE PAYMENT_CAT1_ID = 0 ORDER BY PAYMENT_CAT1_ID'
        ret = self.curs.execute(sqlStr)
        ret = ret.fetchall()
        return ret

    def getBudgetPlanWithDeleteCat1(self):
        '''功能與getBudgetPlanWithoutDeleteCat1類似，不同的是包含已刪除的一級分類
        已刪除的一級分類對應的預算全為0
        返回的列表的下標與CAT1_ID相同'''
        sqlStr = 'SELECT PAYMENT_CAT1.NAME, BUDGET_PLAN.* FROM PAYMENT_CAT1, BUDGET_PLAN \
            WHERE PAYMENT_CAT1.CAT1_ID = BUDGET_PLAN.PAYMENT_CAT1_ID \
            UNION SELECT "TOTAL", * FROM BUDGET_PLAN WHERE PAYMENT_CAT1_ID = 0 ORDER BY PAYMENT_CAT1_ID'
        ret = self.curs.execute(sqlStr)
        ret = ret.fetchall()
        return ret

    def getPaymentCat1AddupInRange(self, beginTime, endTime):
        '''獲取指定指定之間範圍內的各個一級分類支出的值的和
        返回一個字典，結構為: {cat1Id, addupPaymentVal}'''
        dbStr = 'SELECT PAYMENT_CAT2.CAT1_ID, SUM(PAYMENT_RECORD.AMOUNT) FROM PAYMENT_RECORD, PAYMENT_CAT2 \
            WHERE PAYMENT_RECORD.CAT2_ID = PAYMENT_CAT2.CAT2_ID AND PAYMENT_RECORD.TIMEME BETWEEN %d AND %d GROUP BY PAYMENT_CAT2.CAT1_ID'\
            %(beginTime, endTime)
        ret = self.curs.execute(dbStr)
        payment = ret.fetchall()
        retDict = {}
        for item in payment:
            retDict[item[0]] = item[1]
        return retDict

    def getPaymentTotalInRange(self, beginTime, endTime):
        '''獲取指定時間範圍內所有支出的總合'''
        dbStr = 'SELECT SUM(PAYMENT_RECORD.AMOUNT) FROM PAYMENT_RECORD PAYMENT_RECORD, PAYMENT_CAT2 \
            WHERE PAYMENT_RECORD.CAT2_ID = PAYMENT_CAT2.CAT2_ID AND PAYMENT_RECORD.TIMEME BETWEEN %d AND %d'%(beginTime, endTime)
        ret = self.curs.execute(dbStr)
        ret = ret.fetchall()[0][0]
        if ret == None:
            return 0
        return ret

    def deleteAllData(self):
        ''' 刪除所有的數據，但是保留SCHEMA'''
        dbStrs = []
        dbStrs.append('BEGIN TRANSACTION')
        dbStrs.append('DELETE FROM INCOME_RECORD;')
        dbStrs.append('DELETE FROM PAYMENT_RECORD;')
        dbStrs.append('DELETE FROM TRANSFER_RECORD;')
        dbStrs.append('DELETE FROM DAILY_STAT_RECORD;')
        dbStrs.append('DELETE FROM MONTHLY_STAT_RECORD;')
        dbStrs.append('DELETE FROM ANNUALLY_STAT_RECORD;')
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = 0 WHERE ID = 1')
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = 0 WHERE ID = 2')
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = 0 WHERE ID = 3')
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = 0 WHERE ID = 4')
        dbStrs.append('UPDATE ACCOUNT_BALANCE SET BALANCE = 0 WHERE ID = 5')
        dbStrs.append('COMMIT TRANSACTION')
        for dbStr in dbStrs:
            self.curs.execute(dbStr)
        self.db.commit()

    def getRecordInRangeCount(self, beginTime, endTime):
        '''獲取指定之間範圍內的所有記錄的個數，包括收入，支出，轉賬'''
        dbStr = 'SELECT SUM(COUNTT) FROM (SELECT COUNT(*) AS COUNTT FROM INCOME_RECORD WHERE TIMEME BETWEEN %d AND %d\
            UNION SELECT COUNT(*) AS COUNTT FROM PAYMENT_RECORD WHERE TIMEME BETWEEN %d AND %d\
            UNION SELECT COUNT(*) AS COUNTT FROM TRANSFER_RECORD WHERE TIMEME BETWEEN %d AND %d)'\
            %(beginTime ,endTime ,beginTime ,endTime ,beginTime ,endTime)
        ret = self.db.execute(dbStr)
        count = ret.fetchone()[0]
        return count

    def getRecordInRange(self, beginTime, endTime, offset, count):
        '''獲取指定之間範圍內的所有記錄，包括收入，支出，轉賬
        返回一個列表，其中每一項的元素也是一個列表，內容為：
        RCD_ID,TIME,RCD_TYPE,ACCOUNT,CAT_ID,AMOUNT,DESCRIBE
        转账的ACCOUNT为0，支出的CAT_ID为CAT2_ID，其余为CAT1_ID'''
        dbStr = 'SELECT * FROM(SELECT RCD_ID,TIMEME, 1 AS RCD_TYPE,ACCOUNT_ID,CAT1_ID AS CAT_ID,AMOUNT,DESCRIBE FROM INCOME_RECORD WHERE TIMEME BETWEEN %d AND %d UNION\
                SELECT RCD_ID,TIMEME, 2 AS RCD_TYPE,ACCOUNT_ID,CAT2_ID AS CAT_ID,AMOUNT,DESCRIBE FROM PAYMENT_RECORD WHERE TIMEME BETWEEN %d AND %d UNION\
                SELECT RCD_ID,TIMEME, 3 AS RCD_TYPE,0 AS ACCOUNT_ID,CAT1_ID AS CAT_ID,AMOUNT,DESCRIBE FROM TRANSFER_RECORD WHERE TIMEME BETWEEN %d AND %d) ORDER BY TIMEME\
                LIMIT %d OFFSET %d'%(beginTime, endTime, beginTime, endTime, beginTime, endTime, count, offset)
        ret = self.db.execute(dbStr)
        ret = ret.fetchall()
        return ret

    def getRecord(self, recordId, recordType):
        '''获取某一个记录的详细内容
        return: IncomeRecord / PaymentRecord / TransferRecord'''
        if recordType == RecordType.Income:
            return self.getIncomeRecord(recordId)
        if recordType == RecordType.Payment:
            return self.getPaymentRecord(recordId)
        if recordType == RecordType.Transfer:
            return self.getTransferRecord(recordId)
        else:
            raise ValueError()

    def getIncomeRecord(self, recordId):
        '''获取指定ID的收入记录，返回IncomeRecord'''
        dbStr = 'SELECT ACCOUNT_ID,TIMEME,AMOUNT,CAT1_ID,DESCRIBE FROM INCOME_RECORD WHERE RCD_ID = %d'%(recordId)
        ret = self.db.execute(dbStr)
        ret = ret.fetchall()[0]
        if len(ret) == 0 :
            raise ValueError('NO RECORD!')
        incomeRecord = IncomeRecord()
        incomeRecord.accountId = ret[0]
        incomeRecord.time = ret[1]
        incomeRecord.amount = ret[2]
        incomeRecord.cat1Id = ret[3]
        incomeRecord.description = ret[4]
        return incomeRecord

    def getPaymentRecord(self, recordId):
        '''获取指定ID的支出记录，返回PaymentRecord'''
        dbStr = 'SELECT ACCOUNT_ID,TIMEME,AMOUNT,CAT2_ID,DESCRIBE FROM PAYMENT_RECORD WHERE RCD_ID = %d'%(recordId)
        ret = self.db.execute(dbStr)
        ret = ret.fetchall()[0]
        if len(ret) == 0 :
            raise ValueError('NO RECORD!')
        paymentRecord = PaymentRecord()
        paymentRecord.accountId = ret[0]
        paymentRecord.time = ret[1]
        paymentRecord.amount = ret[2]
        paymentRecord.cat2Id = ret[3]
        paymentRecord.cat1Id = Global.categorys.paymentCat2[paymentRecord.cat2Id].cat1Id
        paymentRecord.description = ret[4]
        return paymentRecord

    def getTransferRecord(self, recordId):
        '''获取指定ID的转账记录，返回TransferRecord'''      
        dbStr = 'SELECT TIMEME,AMOUNT,CAT1_ID,SRC_ACCOUNT_ID,DST_ACCOUNT_ID,DESCRIBE FROM TRANSFER_RECORD WHERE RCD_ID = %d'%(recordId)
        ret = self.db.execute(dbStr)
        ret = ret.fetchall()[0]
        if len(ret) == 0 :
            raise ValueError('NO RECORD!')
        transferReord = TransferRecord()
        transferReord.time = ret[0]
        transferReord.amount = ret[1]
        transferReord.cat1Id = ret[2]
        transferReord.srcAccountId = ret[3]
        transferReord.dstAccountId = ret[4]
        transferReord.desctiption = ret[5]
        return transferReord

    def __getIncomeCat1(self):
        dbStr = "SELECT CAT1_ID, NAME, SHOWABLE FROM INCOME_CAT1 ORDER BY CAT1_ID ASC;"
        ret = self.curs.execute(dbStr)
        cat1s = [None]
        cat1 = Cat1()
        for i in ret:
            cat1.id = i[0]
            cat1.name = i[1]
            cat1.showable = i[2]
            cat1s.append(copy.deepcopy(cat1))
        return cat1s

    def __getPaymentCat1(self):
        dbStr = "SELECT CAT1_ID, NAME, SHOWABLE FROM PAYMENT_CAT1 ORDER BY CAT1_ID ASC;"
        ret = self.curs.execute(dbStr)
        cat1s = [None]
        cat1 = Cat1()
        for i in ret:
            cat1.id = i[0]
            cat1.name = i[1]
            cat1.showable = i[2]
            cat1s.append(copy.deepcopy(cat1))
        cat2 = Cat2()
        for i in range(1, len(cat1s)):
            dbStr = 'SELECT CAT2_ID, NAME, SHOWABLE FROM PAYMENT_CAT2 WHERE CAT1_ID = %d;'%(cat1s[i].id)
            ret = self.curs.execute(dbStr)
            for j in ret:
                cat2.id = j[0]
                cat2.name = j[1]
                cat2.showable = j[2]
                cat1s[i].cat2List.append(copy.deepcopy(cat2))
        return cat1s

    def __getPaymentCat2(self):
        dbStr = 'SELECT CAT2_ID, CAT1_ID, NAME FROM PAYMENT_CAT2 ORDER BY CAT2_ID ASC'
        ret = self.curs.execute(dbStr)
        cat2s = [None]
        cat2 = Cat2Simple()
        for i in ret:
            cat2.name = i[2]
            cat2.cat1Id = i[1]
            cat2s.append(copy.deepcopy(cat2))
        return cat2s

    def __getTransferCat1(self):
        dbStr = 'SELECT CAT1_ID, NAME, SHOWABLE FROM TRANSFER_CAT1 ORDER BY CAT1_ID ASC'
        ret = self.curs.execute(dbStr)
        cat1s = [None]
        cat1 = Cat1()
        for i in ret:
            cat1.id = i[0]
            cat1.name = i[1]
            cat1.showable = i[2]
            cat1s.append(copy.deepcopy(cat1))
        return cat1s

def accountIdToString(accountId):
    if type(accountId) != int:
        raise ValueError("type must be int type!")
    if accountId == 2 :
        return "DEBT"
    elif accountId == 1 :
        return "CASH"
    elif accountId == 3 :
        return "REDEBT"
        raise ValueError("account type value is not correct!")

def nextTimeStemp(currentTime, timeLength):
    '''根據當前秒級時間戳和事件跨度，一個事件跨度后的時間戳'''
    if timeLength == TimeLength.Day:
        return currentTime + 3600 * 24
    if timeLength == TimeLength.Month:
        ts = time.localtime(currentTime)
        month = ts.tm_mon
        year = ts.tm_year
        if month < 12:
            month += 1
        else:
            month = 1
            year += 1
        return int(time.mktime(time.strptime('%d-%d'%(year,month), '%Y-%m')))
    if timeLength == TimeLength.Year:
        ts = time.localtime(currentTime)
        return int(time.mktime(time.strptime('%d'%(ts.tm_year + 1), '%Y')))
    else:
        raise ValueError()

def supplyStatisticRecord(records, beginTime, endTime, timeLength):
    '''補充從STAT_RECORDS中獲取到的records中缺少的項，時間以外的字段都置為零
    timeLength: 時間跨度，值為TimeLength中所描述的'''
    j = 0
    t = beginTime
    output = []
    while t <= endTime:
        if j < len(records) and t == records[j][0]:
            output.append(records[j])
            j += 1
        else:
            output.append([t, 0, 0])
        t = nextTimeStemp(t, timeLength)
    if j < len(records):
        raise ValueError('records\' time is not fit')
    return output
